package dao;

import java.sql.*;

public class UserDao {

    //新增用户
    public int insert(String username,String password) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL"
                , "system"
                , "000000");
        String sql="insert into j_user values (seq_j_user.nextval,'"+username+"','"+password+"',1)";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
        return 0;
    }
    public int update(int id,String username,String password ) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL"
                , "system",
                "000000");
        String sql="update j_user set username=?,password=?  where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,username);
        statement.setString(2,password);
        statement.setInt(3,id);
        statement.executeUpdate();

        statement.close();
        connection.close();
        return 0;
    }
    public int delete(int id) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL"
                , "system",
                "000000");
        String sql="delete from j_user where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.execute();
        statement.close();
        connection.close();
        return 0;
    }
    public int select(int id) throws ClassNotFoundException, SQLException {
        //1.加载驱动类
        //2.创建连接 获取连接对象
        //3,创建SQL语句
        //4.执行sql语句
        Class.forName("oracle.jdbc.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL"
                , "system",
                "000000");
        String sql = "select * from j_user where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            int i = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            int state = resultSet.getInt("state");
            System.out.println("id:"+i+" username:"+username+ " password:"+password+" state:"+state);
        }
        statement.close();
        connection.close();
        resultSet.close();
        return 0;
    }
}
